package cp3120.dao.mysql;

import cp3120.dao.*;
import cp3120.model.Password;
import cp3120.model.Permission;
import cp3120.model.Role;
import cp3120.model.User;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
/**
 *
 * @author Adam Scarr <scarr.adam@gmail.com>
 */
public class MySQLUserDAO implements UserDAO {
    private Connection db;
    PreparedStatement getUsers;
    PreparedStatement deleteUser;
    PreparedStatement deleteRole;
    PreparedStatement deleteAllUserRoles;
    PreparedStatement deleteAllRolePermissions;
    PreparedStatement addRole;
    PreparedStatement addUser;
    PreparedStatement addUserRole;
    PreparedStatement addRolePermission;
    PreparedStatement getPermissions;
    PreparedStatement updateRole;

    /**
     * constructor, takes a database connection and calls private member to prepare the My SQL statements.
      * @param db the database that will be accessed
     */
    public MySQLUserDAO(Connection db) {
        this.db = db;
        prepareStatements();
    }

    private void prepareStatements() {
        try {
            getUsers = db.prepareStatement("SELECT userID, username, password, f_name, l_name, home_address, mail_address, phone_num, email_address FROM user;");
            deleteUser = db.prepareStatement("DELETE FROM user WHERE userID = ?");
            deleteRole = db.prepareStatement("DELETE FROM role WHERE id = ?");
            deleteAllUserRoles = db.prepareStatement("DELETE FROM usersroles WHERE userID = ?");
            deleteAllRolePermissions = db.prepareStatement("DELETE FROM rolespermissions WHERE roleID = ?");
            addRole = db.prepareStatement("INSERT INTO role (name) VALUES (?)", Statement.RETURN_GENERATED_KEYS);
            addUserRole = db.prepareStatement("INSERT INTO usersroles (userID, roleID) VALUES (?, ?)");
            addRolePermission = db.prepareStatement("INSERT INTO rolespermissions (roleID, permissionID) VALUES (?, ?)");
            getPermissions = db.prepareStatement("SELECT id, name FROM permission;");
            updateRole = db.prepareStatement("UPDATE role SET name = ? WHERE id = ?");
            addUser = db.prepareStatement("INSERT INTO user (username, password, f_name, l_name, home_address, mail_address, phone_num, email_address) VALUES " +
                                          "(?, ?, ?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    /**
     * Gets a user from the database
     * @param username The username of the user to be retrived
     * @return a user object, else null if the user could not be retreived
     */
    public User getUser(String username) {
        User user = null;
        try {
            Statement stat = db.createStatement();
            String query = "SELECT * FROM user WHERE username = '" + username + "'";
            ResultSet result = stat.executeQuery(query);

            if (result.next()) {
                user = buildUserFromResult(result);
            }

        } catch (SQLException e) {
            System.out.println("[MySQL UserDAO] SQLException: " + e.getMessage());
            System.out.println("[MySQL UserDAO] SQLState:     " + e.getSQLState());
            System.out.println("[MySQL UserDAO] VendorError:  " + e.getErrorCode());
        }

        if (user == null) {
            System.out.println("[MySQL UserDAO] Failed to load user data.");
        }
        return user;
    }

    /**
     * gets a user from the database
     * @param userID the ID of the user to be retreived
     * @return a user object, else null if the user could not be retreived
     */
    public User getUserByID(int userID) {
        System.out.println("Checking user from database.");
        User user = null;
        try {
            Statement stat = db.createStatement();
            String query = "SELECT * FROM user WHERE userID = '" + userID + "'";
            ResultSet result = stat.executeQuery(query);

            if (result.next()) {
                user = buildUserFromResult(result);
            }

        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState:     " + e.getSQLState());
            System.out.println("VendorError:  " + e.getErrorCode());
        }

        if (user == null) {
            System.out.println("Failed to load user data.");
        } else {
            System.out.println("User data loaded.");
        }
        return user;
    }

    /**
     * gets a list of all users in the database
     * @return a list of users
     */
    public List<User> getUsers() {
        List<User> users = new ArrayList<User>();

        try {
            ResultSet result = getUsers.executeQuery();

            while (result.next()) {
                users.add(buildUserFromResult(result));
            }

        } catch (SQLException e) {
            System.out.println("[MySQL UserDAO] SQLException: " + e.getMessage());
            System.out.println("[MySQL UserDAO] SQLState:     " + e.getSQLState());
        }

        return users;
    }

    private User buildUserFromResult(ResultSet rs) throws SQLException {
        User user = new User();
        user.setUserID(rs.getInt("userID"));
        user.setUsername(rs.getString("username"));
        user.setPassword(new Password(rs.getString("password"), true));
        user.setFirstName(rs.getString("f_name"));
        user.setLastName(rs.getString("l_name"));
        user.setHomeAddress(rs.getString("home_address"));
        user.setMailingAddress(rs.getString("mail_address"));
        user.setContactPhone(rs.getString("phone_num"));
        user.setContactEmail(rs.getString("email_address"));

        user.setRoles(getUserRoles(user.getUserID()));

        return user;
    }

    private List<Role> getUserRoles(int userID) throws SQLException {
        Statement rolesStatement = db.createStatement();
        String rolesQuery = "SELECT id, name " +
                            "FROM role JOIN usersroles ON role.id = usersroles.roleID " +
                            "WHERE userID = '" + userID + "'";
        ResultSet rolesResult = rolesStatement.executeQuery(rolesQuery);

        List<Role> roles = new LinkedList<Role>();
        while (rolesResult.next()) {
            int id = rolesResult.getInt(1);
            String name = rolesResult.getString(2);

            Statement permissionsStatement = db.createStatement();
            String permissionsQuery = "SELECT id, name FROM permission " +
                                      "JOIN rolespermissions ON permission.id = rolespermissions.permissionID " +
                                      "WHERE roleID = '" + id + "'";
            ResultSet permissionResult = permissionsStatement.executeQuery(permissionsQuery);

            List<Permission> permissions = new LinkedList<Permission>();
            while (permissionResult.next()) {
                int permID = permissionResult.getInt(1);
                String permName = permissionResult.getString(2);

                permissions.add(new Permission(permID, permName));
            }

            roles.add(new Role(id, name, permissions));
        }

        return roles;
    }

    private boolean setUserRoles(User user) throws SQLException {
        deleteAllUserRoles.setInt(1, user.getUserID());
        boolean status = deleteAllUserRoles.execute();

        for (Role role: user.getRoles()) {
            addUserRole.setInt(1, user.getUserID());
            addUserRole.setInt(2, role.getId());
            boolean addStatus = addUserRole.execute();
            status = status && addStatus;
        }

        return status;
    }

    /**
     * replaces a user in the database with a update version of that user
     * @param user the new user to be added to the database
     * @return true if the user was updated, else false
     */
    public boolean updateUser(User user) {

        try {
            Statement stat = db.createStatement();
            String query = "UPDATE `user` SET " +
                           "`username` = '" + user.getUsername() + "'," +
                           "`password` = '" + user.getPassword() + "'," +
                           "`f_name` = '" + user.getFirstName() + "'," +
                           "`l_name` = '" + user.getLastName() + "'," +
                           "`home_address` = '" + user.getHomeAddress() + "'," +
                           "`mail_address` = '" + user.getMailingAddress() + "'," +
                           "`phone_num` = '" + user.getContactPhone() + "'," +
                           "`email_address` = '" + user.getContactEmail() + "' " +
                           "WHERE `userID` = " + user.getUserID();
            stat.executeUpdate(query);

            setUserRoles(user);

        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    /**
     * adds a new user to the database
     * @param user the user to be added
     * @return true if the user was added, else false
     */
    public boolean addUser(User user) {

        try {
            addUser.setString(1, user.getUsername());
            addUser.setString(2, user.getPassword().getPassword());
            addUser.setString(3, user.getFirstName());
            addUser.setString(4, user.getLastName());
            addUser.setString(5, user.getHomeAddress());
            addUser.setString(6, user.getMailingAddress());
            addUser.setString(7, user.getContactPhone());
            addUser.setString(8, user.getContactEmail());
            addUser.execute();

            ResultSet rs = addUser.getGeneratedKeys();
            int id = 0;
            if (rs != null && rs.next()) {
                id = rs.getInt(1);
            }

            // Add user to default role.
            addUserRole.setInt(1, id);
            addUserRole.setInt(2, 1);

            addUserRole.execute();

            user.setUserID(id);
 
            //setUserRoles(user);

        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }

        return true;
    }

    /**
     * deletes a user from the database
     * @param user the user to be deleted
     * @return true if the user was deleted, else false
     */
    public boolean deleteUser(User user) {

        System.out.println("Deleting user.");
        try {
            deleteUser.setInt(1, user.getUserID());
            return deleteUser.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * gets all roles in the database
     * @return a lsit of roles
     */
    public List<Role> getRoles() {

        List<Role> roles = new LinkedList<Role>();
        try {
            Statement rolesStatement = db.createStatement();
            String rolesQuery = "SELECT id, name FROM role";
            ResultSet rolesResult = rolesStatement.executeQuery(rolesQuery);

            while (rolesResult.next()) {
                int id = rolesResult.getInt(1);
                String name = rolesResult.getString(2);

                Statement permissionsStatement = db.createStatement();
                String permissionsQuery = "SELECT id, name FROM permission " +
                                          "JOIN rolespermissions ON permission.id = rolespermissions.permissionID " +
                                          "WHERE roleID = '" + id + "'";
                ResultSet permissionResult = permissionsStatement.executeQuery(permissionsQuery);

                List<Permission> permissions = new LinkedList<Permission>();
                while (permissionResult.next()) {
                    int permID = permissionResult.getInt(1);
                    String permName = permissionResult.getString(2);

                    permissions.add(new Permission(permID, permName));
                }

                roles.add(new Role(id, name, permissions));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return roles;
    }

    /**
     * replaces a role in the database with an update version of that role
     * @param role the role to be updated
     * @return true if the roll was updated, else false
     */
    public boolean updateRole(Role role) {

        boolean updateSuccess;

        boolean updatePermissionSuccess = true;
        try {
            updateRole.setString(1, role.getName());
            updateRole.setInt(2, role.getId());
            updateSuccess = updateRole.execute();

            deleteAllRolePermissions.setInt(1, role.getId());
            updatePermissionSuccess = deleteAllRolePermissions.execute();

            for (Permission permission: role.getPermissions()) {
                addRolePermission.setInt(1, role.getId());
                addRolePermission.setInt(2, permission.getId());
                boolean success = addRolePermission.execute();

                updatePermissionSuccess = updatePermissionSuccess && success;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }

        return updateSuccess && updatePermissionSuccess;
    }

    /**
     * deletes a role in the database
     * @param role the role to be deleted
     * @return true if the role was deleted, else false
     */
    public boolean deleteRole(Role role) {

        try {
            deleteRole.setInt(1, role.getId());
            boolean roleStatus = deleteRole.execute();
            deleteAllRolePermissions.setInt(1, role.getId());
            boolean permStatus = deleteAllRolePermissions.execute();

            return roleStatus && permStatus;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return false;
    }

    /**
     * adds a new role to the database
     * @param role the role to be added to the database
     * @return true if the roll was added, else false
     */
    public boolean addRole(Role role) {

        boolean success = true;
        try {
            addRole.setString(1, role.getName());
            success = addRole.execute();

            ResultSet rs = addRole.getGeneratedKeys();
            int id = 0;
            if (rs != null && rs.next()) {
                id = rs.getInt(1);
            }

            for (Permission permission: role.getPermissions()) {
                System.out.println(":: Adding permission to role " + id + ", " + permission.getId() );
                addRolePermission.setInt(1, id);
                addRolePermission.setInt(2, permission.getId());
                boolean success2 = addRolePermission.execute();
                success = success && success2;
            }

        } catch (SQLException e) {
            e.printStackTrace();
            success = false;
        }

        return success;
    }

    /**
     * returns a list of all positions in the database
     * @return a list of positions
     */
    public List<Permission> getPermissions() {

        LinkedList<Permission> permissions = new LinkedList<Permission>();
        try {
            ResultSet rs = getPermissions.executeQuery();

            while (rs.next()) {
                permissions.add(new Permission(rs.getInt(1), rs.getString(2)));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return permissions;
    }
}
